﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SQLite;
using System.Data;

namespace FMWeb.CSCode
{
    public class ScoreManager
    {

        public static int SubmitScore(string name, long score)
        {
            using (SQLiteConnection c = DBManager.GetDBConnection())
            {
                SQLiteCommand command = new SQLiteCommand("INSERT INTO [Scores] (Name,Score) VALUES (@name,@score)", c);
                command.Parameters.AddWithValue("name", name);
                command.Parameters.AddWithValue("score", score);

                c.Open();
                command.ExecuteNonQuery();
            }
            UpdateHighscoreCache();
            return GetHighScores().IndexOf(GetHighScores().Where(s => s.name == name && s.highScore == score).First());
        }

        public static List<Highscore> GetHighScores() 
        {
            if (HttpRuntime.Cache["scores"] == null)
            {
                UpdateHighscoreCache();
            }

            return (List<Highscore>)HttpRuntime.Cache["scores"];
            
        }

        private static void UpdateHighscoreCache()
        {
            List<Highscore> result = new List<Highscore>();
            DataTable dt = new DataTable();
            using (SQLiteConnection c = DBManager.GetDBConnection())
            {
                SQLiteDataAdapter command = new SQLiteDataAdapter(@"SELECT *, (
                SELECT count(*) 
                FROM [Scores] as s2
                WHERE s2.Score > s1.Score
                ) as rank 
            FROM [Scores] as s1 ORDER BY [Score] DESC, [When]", c);

                c.Open();
                command.Fill(dt);
            }

            foreach (DataRow row in dt.Rows)
            {
                Highscore score = new Highscore
                {
                    rank = (long)row["rank"] + 1,
                    name = (string)row["name"],
                    highScore = (long)row["score"],
                    when = (DateTime)row["when"]
                };
                result.Add(score);
            }
            HttpRuntime.Cache["scores"] = result;
        }
        
        
                          
    }

    public class Highscore
    {
        public long rank;
        public string name;
        public long highScore;
        public DateTime when;
    }
}
